package com.example.sp.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.sp.entity.Consume;
import com.example.sp.entity.StuCost;
import com.example.sp.service.ConsumeService;
import com.example.sp.service.StuCostService;
import com.example.sp.utils.Res;
import com.example.sp.utils.Result;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/cost")
@AllArgsConstructor
@Slf4j
public class StuCostController {
    private final StuCostService stuCostService;
    private final ConsumeService consumeService;

    /**
     * 查询每个年级 的 每个月的消费
     * @return
     */
    @RequestMapping("/ConsumeAll")
    public Result select11(){
        List<Consume> conList=consumeService.consume1("2018级");
        List<Consume> conList1=consumeService.consume1("2019级");
        List<Consume> conList2=consumeService.consume1("2017级");
        List<Consume> conList3=consumeService.consume1("2016级");
        Map<String,Object> map1=new HashMap<>();
        map1.put("big2",conList);
        map1.put("big1",conList1);
        map1.put("big3",conList2);
        map1.put("big4",conList3);
        return Res.ok(map1);
    }

    /**
     *省份
     * @return 总消费 省 城市
     */
    @RequestMapping("/cost1")
    public Result Select1(){
        return Res.ok(stuCostService.listMaps(new QueryWrapper<StuCost>().select(
                "sum(cost_number) as money","province","city").groupBy("province")
        ));
    }

    /**
     *  学院
     * @return 学院的男女
     */
    @RequestMapping("/SexCollege")
    public Result Select2(String college){
        Map<String,Object> l1=stuCostService.getMap(new QueryWrapper<StuCost>().select(
                "college",
                "SUM(IF(stu_sex='男',cost_number,0)) as 'ManConsume'",
                "SUM(IF(stu_sex='女',cost_number,0)) as 'WomenConsume'"
                 ).eq("college",college));
        return Res.ok(l1);

    }
    /**
     * grade年级
     * @return 总消费 年级
     */
    @RequestMapping("/SexGrade")
    public Result Select3(String grade){
        Map<String,Object> l2=stuCostService.getMap(new QueryWrapper<StuCost>().select(
                "grade",
                "SUM(IF(stu_sex='男',cost_number,0)) as 'ManConsume'",
                "SUM(IF(stu_sex='女',cost_number,0)) as 'WomenConsume'"
        ).groupBy("grade").eq("grade",grade));
        return Res.ok(l2);
    }
    /**
     *
     * @return 各年级 男女 总消费3333333333
     */
    @RequestMapping("/SexGrade1")
    public Result Select4(){
        List<Map<String,Object>> l3=stuCostService.listMaps(new QueryWrapper<StuCost>().select(
                "grade",
                "Sum(cost_number) as ConsumeAll",
                "SUM(IF(stu_sex='男',cost_number,0)) as 'ManConsume'",
                "SUM(IF(stu_sex='女',cost_number,0)) as 'WomenConsume'"
        ).groupBy("grade"));
        return Res.ok(l3);
    }
    /**
     *
     * @return 各省份 总消费
     */
    @RequestMapping("/procost")
    public Result Select41(){
        List<Map<String,Object>> l3=stuCostService.listMaps(new QueryWrapper<StuCost>().select(
                "province",
                "Sum(cost_number) as ConsumeAll"
        ).groupBy("province"));
        return Res.ok(l3);
    }

    /**
     *
     * @return 全校男女比例
     */
    @RequestMapping("/SexAll")
    public Result Select3(){
        return Res.ok(stuCostService.listMaps(new QueryWrapper<StuCost>().select(
                "SUM(IF(stu_sex='男',cost_number,0)) as 'ManConsume'",
                "SUM(IF(stu_sex='女',cost_number,0)) as 'WomenConsume'")
        ));

    }

    /**
     * CT MONTH(cost_data) AS 'momth',SUM(cost_number) as 'money' FROM stu_cost WHERE
     * grade='2018级' and YEAR(cost_data)='2019' GROUP BY month(cost_data)
     * @return
     */
    @RequestMapping("/cost4")
    public Result Select4(String year,String grade) {
        return Res.ok(stuCostService.listMaps(new QueryWrapper<StuCost>().select("month(cost_data) as 'month'", "sum(cost_number) as 'money'")
                .eq("grade", grade).eq("YEAR(cost_data)", year).groupBy("month(cost_data)")
        ));
    }
}


